import pymysql
import json

mainDb = pymysql.connect(host='localhost', port=23306, user='root', password='Pr0dkcd_', db="kcdoc")
db_cursor = mainDb.cursor()

difDb = pymysql.connect(host='10.2.5.100', port=13306, user='root', password='kc@#2023++', db="kcdoc")
dif_cursor = difDb.cursor()


def getMainPlanInfo():
    main_plan_info = """
      SELECT plan.id,
             plan.production_code,
             plan.production_plan_name,
             plan.chartNo,
              plan.sorties_batch_no 
              FROM kc_production_plan plan
      left join kc_production_plan_process kppp on plan.id = kppp.production_plan_id and plan.process = kppp.id
      WHERE LEFT(production_code,'2')= 'JF' 
      
      and kppp.process_index > 11
    """
    db_cursor.execute(main_plan_info)
    main_info = db_cursor.fetchall()
    set_of_dicts = []
    for plan in main_info:
        plan_zip = {}
        plan_id = plan[0]
        plan_code = plan[1]
        plan_name = plan[2]
        plan_chartNo = plan[3]
        plan_sorties_batch_no = plan[4]
        plan_zip["planId"] = plan_id
        plan_zip["plan_code"] = plan_code
        plan_zip["plan_name"] = plan_name
        plan_zip["plan_chartNo"] = plan_chartNo
        plan_zip["plan_sorties_batch_no"] = plan_sorties_batch_no
        set_of_dicts.append(plan_zip)

    return set_of_dicts


def getDifDbPlanInfo():
    main_plan_info = """
      SELECT id,production_code,production_plan_name,chartNo, sorties_batch_no FROM kc_production_plan 
      WHERE LEFT(production_code,'2')= 'JF'       
    """
    dif_cursor.execute(main_plan_info)
    main_info = dif_cursor.fetchall()
    set_of_dicts = []
    for plan in main_info:
        plan_zip = {}
        plan_id = plan[0]
        plan_code = plan[1]
        plan_name = plan[2]
        plan_chartNo = plan[3]
        plan_sorties_batch_no = plan[4]
        plan_zip["planId"] = plan_id
        plan_zip["plan_code"] = plan_code
        plan_zip["plan_name"] = plan_name
        plan_zip["plan_chartNo"] = plan_chartNo
        plan_zip["plan_sorties_batch_no"] = plan_sorties_batch_no
        set_of_dicts.append(plan_zip)

    return set_of_dicts


def updateSql(dicts):
    for info in dicts:
        updateSql = """
        update kc_production_plan set production_plan_name = '{0}',  chartNo = '{1}',sorties_batch_no = '{2}' where id = {3};
      """.format(info['plan_name'], info['plan_chartNo'], info['plan_sorties_batch_no'], info['planId'])
        print(updateSql)


if __name__ == '__main__':
    new_dicts = []
    main_plan_info = getMainPlanInfo()
    dif_plan_info = getDifDbPlanInfo()
    for main_info in dif_plan_info:
        corresponding_dict = next((end_dict for end_dict in main_plan_info
                                   if end_dict["planId"] == main_info["planId"]), None)
        if corresponding_dict:
            new_dict = main_info.copy()
            for key in main_info:
                if main_info[key] != corresponding_dict[key]:
                    new_dict[key] = corresponding_dict[key]
            if new_dict != main_info:  # Only append if changes were made
                new_dicts.append(new_dict)
        else:
            new_dicts.append(main_info)

    print(new_dicts)
    print(json.dumps(new_dicts))
    updateSql(new_dicts)
